package com.scuec.groupfive.mapper;

import com.scuec.groupfive.entity.FavoriteDetail_z;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.List;

@Mapper
public interface FavoriteDetailMapper_z {
    Logger logger = LoggerFactory.getLogger(FavoriteDetailMapper_z.class);

    @Select("SELECT " +
            "f.favorite_id AS id, " +
            "f.user_id AS userId, " +
            "f.question_id AS questionId, " +
            "f.folder_id AS folderId, " +
            "f.status, " +
            "f.created_at AS createdAt, " +
            "f.updated_at AS updatedAt, " +
            "q.question_type AS questionType, " +
            "q.content, " +
            "q.year, " +
            "q.original_sequence AS originalSequence, " +
            "q.subject_id AS subjectId, " +
            "q.type_id AS typeId, " +
            "q.chapter_id AS chapterId, " +
            "q.keyword, " +
            "q.creator_id AS creatorId, " +
            "q.status AS questionStatus, " +
            "q.created_at AS questionCreatedAt, " +
            "q.updated_at AS questionUpdatedAt, " +
            "s.subject_name AS subjectName, " +
            "CASE q.question_type " +
            "  WHEN 'single_choice' THEN cq.options " +
            "  ELSE NULL " +
            "END AS options, " +
            "CASE q.question_type " +
            "  WHEN 'single_choice' THEN cq.answer " +
            "  WHEN 'fill_in_the_blank' THEN fbq.answer_template " +
            "  WHEN 'subjective' THEN sq.reference_answer " +
            "  ELSE NULL " +
            "END AS answer, " +
            "CASE q.question_type " +
            "  WHEN 'single_choice' THEN cq.analysis " +
            "  WHEN 'fill_in_the_blank' THEN fbq.analysis " +
            "  WHEN 'subjective' THEN sq.analysis " +
            "  ELSE NULL " +
            "END AS analysis " +
            "FROM favorites f " +
            "JOIN questions q ON f.question_id = q.question_id " +
            "LEFT JOIN subjects s ON q.subject_id = s.subject_id " +
            "LEFT JOIN choice_questions cq ON q.question_id = cq.question_id AND q.question_type = 'single_choice' " +
            "LEFT JOIN fill_blank_questions fbq ON q.question_id = fbq.question_id AND q.question_type = 'fill_in_the_blank' " +
            "LEFT JOIN subjective_questions sq ON q.question_id = sq.question_id AND q.question_type = 'subjective' " +
            "WHERE f.user_id = #{userId} AND f.status = 1")
    List<FavoriteDetail_z> getFavoriteDetails(@Param("userId") Long userId);

    @Select("<script>" +
            "SELECT f.favorite_id as id, f.user_id as userId, f.question_id as questionId, f.folder_id as folderId, " +
            "f.status, f.created_at as createdAt, f.updated_at as updatedAt, " +
            "q.content, q.question_type as questionType, q.difficulty, " +
            "q.year, q.original_sequence as originalSequence, " +
            "q.subject_id as subjectId, q.type_id as typeId, q.chapter_id as chapterId, q.keyword, " +
            "q.creator_id as creatorId, q.status as questionStatus, " +
            "q.created_at as questionCreatedAt, q.updated_at as questionUpdatedAt, " +
            "s.subject_name as subjectName, " +
            "CASE q.question_type " +
            "  WHEN 'single_choice' THEN cq.options " +
            "  ELSE NULL " +
            "END AS options, " +
            "CASE q.question_type " +
            "  WHEN 'single_choice' THEN cq.answer " +
            "  WHEN 'fill_in_the_blank' THEN fbq.answer_template " +
            "  WHEN 'subjective' THEN sq.reference_answer " +
            "  ELSE NULL " +
            "END AS answer, " +
            "CASE q.question_type " +
            "  WHEN 'single_choice' THEN cq.analysis " +
            "  WHEN 'fill_in_the_blank' THEN fbq.analysis " +
            "  WHEN 'subjective' THEN sq.analysis " +
            "  ELSE NULL " +
            "END AS analysis " +
            "FROM favorites f " +
            "JOIN questions q ON f.question_id = q.question_id " +
            "LEFT JOIN subjects s ON q.subject_id = s.subject_id " +
            "LEFT JOIN choice_questions cq ON q.question_id = cq.question_id AND q.question_type = 'single_choice' " +
            "LEFT JOIN fill_blank_questions fbq ON q.question_id = fbq.question_id AND q.question_type = 'fill_in_the_blank' " +
            "LEFT JOIN subjective_questions sq ON q.question_id = sq.question_id AND q.question_type = 'subjective' " +
            "WHERE f.user_id = #{userId} AND f.status = 1 " +
            "<if test='folderId != null'>" +
            "   AND f.folder_id = #{folderId} " +
            "</if>" +
            "<if test='query != null and query != \"\"'>" +
            "   AND q.content LIKE CONCAT('%', #{query}, '%')" +
            "</if>" +
            "ORDER BY f.created_at DESC " +
            "LIMIT #{offset}, #{limit}" +
            "</script>")
    List<FavoriteDetail_z> getFavoriteQuestions(
            @Param("userId") Long userId,
            @Param("folderId") Long folderId,
            @Param("offset") int offset,
            @Param("limit") int limit,
            @Param("query") String query
    );

    @Select("<script>" +
            "SELECT COUNT(*) " +
            "FROM favorites f " +
            "JOIN questions q ON f.question_id = q.question_id " +
            "WHERE f.user_id = #{userId} AND f.status = 1 " +
            "<if test='folderId != null'>" +
            "   AND f.folder_id = #{folderId} " +
            "</if>" +
            "<if test='query != null and query != \"\"'>" +
            "   AND q.content LIKE CONCAT('%', #{query}, '%')" +
            "</if>" +
            "</script>")
    int getFavoriteQuestionsCount(
            @Param("userId") Long userId,
            @Param("folderId") Long folderId,
            @Param("query") String query
    );

    // 添加一个简单的查询来检查收藏数据
    @Select("SELECT COUNT(*) FROM favorites WHERE user_id = #{userId}")
    int checkFavoriteCount(@Param("userId") Long userId);

    // 添加一个查询来检查收藏状态
    @Select("SELECT COUNT(*) FROM favorites WHERE user_id = #{userId} AND status = 1")
    int checkActiveFavoriteCount(@Param("userId") Long userId);

    // 移除收藏的题目（软删除）
    @Update("UPDATE favorites SET status = 0 WHERE user_id = #{userId} AND question_id = #{questionId}")
    void removeFavoriteQuestion(@Param("userId") Long userId, @Param("questionId") Long questionId);

    // 新增：根据题目ID获取完整题目详情（用于题目详情页面）
    @Select("SELECT " +
            "f.favorite_id AS id, " +
            "f.user_id AS userId, " +
            "f.question_id AS questionId, " +
            "f.folder_id AS folderId, " +
            "f.status, " +
            "f.created_at AS createdAt, " +
            "f.updated_at AS updatedAt, " +
            "q.question_type AS questionType, " +
            "q.content, " +
            "q.year, " +
            "q.original_sequence AS originalSequence, " +
            "q.subject_id AS subjectId, " +
            "q.type_id AS typeId, " +
            "q.chapter_id AS chapterId, " +
            "q.keyword, " +
            "q.creator_id AS creatorId, " +
            "q.status AS questionStatus, " +
            "q.created_at AS questionCreatedAt, " +
            "q.updated_at AS questionUpdatedAt, " +
            "s.subject_name AS subjectName, " +
            "CASE q.question_type " +
            "  WHEN 'single_choice' THEN cq.options " +
            "  ELSE NULL " +
            "END AS options, " +
            "CASE q.question_type " +
            "  WHEN 'single_choice' THEN cq.answer " +
            "  WHEN 'fill_in_the_blank' THEN fbq.answer_template " +
            "  WHEN 'subjective' THEN sq.reference_answer " +
            "  ELSE NULL " +
            "END AS answer, " +
            "CASE q.question_type " +
            "  WHEN 'single_choice' THEN cq.analysis " +
            "  WHEN 'fill_in_the_blank' THEN fbq.analysis " +
            "  WHEN 'subjective' THEN sq.analysis " +
            "  ELSE NULL " +
            "END AS analysis " +
            "FROM favorites f " +
            "JOIN questions q ON f.question_id = q.question_id " +
            "LEFT JOIN subjects s ON q.subject_id = s.subject_id " +
            "LEFT JOIN choice_questions cq ON q.question_id = cq.question_id AND q.question_type = 'single_choice' " +
            "LEFT JOIN fill_blank_questions fbq ON q.question_id = fbq.question_id AND q.question_type = 'fill_in_the_blank' " +
            "LEFT JOIN subjective_questions sq ON q.question_id = sq.question_id AND q.question_type = 'subjective' " +
            "WHERE f.user_id = #{userId} AND f.question_id = #{questionId} AND f.status = 1")
    FavoriteDetail_z getFavoriteQuestionDetail(@Param("userId") Long userId, @Param("questionId") Long questionId);
}